﻿namespace SMECustomerWebsite.Controllers
{
    using ClosedXML.Excel;
    using Common.Cache;
    using Common.Contract;
    using Common.Contract.Authentication;
    using Common.Contract.Authentication.Record;
    using Common.Contract.Authentication.Request;
    using Common.Contract.Authentication.Response;
    using Common.Contract.Customer;
    using Common.Contract.Customer.Record;
    using Common.Contract.Customer.Request.CustomerManager;
    using Common.Contract.Customer.Response.CustomerManager;
    using Common.Contract.SystemConfig;
    using Common.Contract.SystemConfig.Record;
    using Common.Contract.SystemConfig.Request;
    using Common.Service;
    using Common.Type;
    using Common.Util;
    using Common.Web.Share;
    using SMECustomerWebsite.Controllers.core;
    using SMECustomerWebsite.Core.Configuration;
    using SMECustomerWebsite.Models.Interface;
    using SMECustomerWebsite.Models.ViewModels;
    using SMECustomerWebsite.Providers;
    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Data.OleDb;
    using System.IO;
    using System.Linq;
    using System.Reflection;
    using System.Text;
    using System.Web;
    using System.Web.Mvc;
    public class ControllerBase : Controller
    {
        public ControllerBase()
        {
            if (this.Provider == null) this.Provider = new ProviderFactory();
        }
        protected List<string> ALLOW_PAGES = new List<string>() { "/GUEST/REQUESTCREATEDAO/", "/HOME/ERROR/", "/ACCOUNT/LOGOUT/", "SYSTEM/CHANGELANGUAGE" };
        protected List<string> ALLOW_FILE_DAO_MANAGEMENT = new List<string> { ".jpg", ".png", ".jpeg", ".pdf" };
        public IProviderFactory Provider { get; private set; }

        #region Load Controller
        protected override IAsyncResult BeginExecuteCore(AsyncCallback callback, object state)
        {
            string lang = null;
            HttpCookie langCookie = Request.Cookies["currentLang"];
            if (langCookie != null)
            {
                lang = langCookie.Value;
            }
            else
            {
                var userLanguage = Request.UserLanguages;
                var userLang = userLanguage != null ? userLanguage[0] : "";
                lang = userLang != "" ? userLang : SiteLanguages.GetCurrentLanguage();
            }
            new SiteLanguages().SetLanguage(lang);
            return base.BeginExecuteCore(callback, state);
        }
        #endregion

        #region Menu
        private List<TreeViewNode> menuNodes;
        public Guid ApplicationKey { get { return SystemConfiguration.ApplicationKey; } }

        public string AccessRoleIdentifier
        {
            get
            {
                return CacheFactory.MemCache.GetFromCache<string>(CACHING_NAME.ROLE_ACCESS_IDENTIFIER, () =>
                {
                    var header = new RequestHeaderRecord
                    {
                        ApplicationKey = SystemConfiguration.ApplicationKey,
                        CallerName = "Authentication.Web.Controller.ControllerBase.AccessRoleIdentifier"
                    };

                    using (ServiceClient<IConfiguration> serviceClient = new ServiceClient<IConfiguration>())
                    {
                        RetrieveDictionaryRequest request = new RetrieveDictionaryRequest
                        {
                            Header = header,
                            Key = SystemConfiguration.RoleLinkAccessKey
                        };
                        var objectResponse = serviceClient.Proxy.RetrieveDictionary(request);

                        if (objectResponse.IsSuccess)
                        {
                            return objectResponse.Record.Value;
                        }
                        else
                        {
                            DictionaryRecord record = new DictionaryRecord { ApplicationKey = SystemConfiguration.ApplicationKey, Key = SystemConfiguration.RoleLinkAccessKey, Value = SystemConfiguration.RoleLinkAccess };
                            AddDictionaryRequest request3 = new AddDictionaryRequest
                            {
                                Header = header,
                                Record = record
                            };
                            var response = serviceClient.Proxy.AddDictionary(request3);
                        }
                    }
                    return SystemConfiguration.RoleLinkAccess;
                }, SystemConfiguration.DefaultCacheTime);
            }
        }

        protected List<TreeViewNode> MenuNodes
        {
            get
            {
                if (menuNodes == null)
                {
                    menuNodes = GetMenuNodes(this.CurrentUser);
                }
                return menuNodes;
            }
        }

        public string CurrentURL
        {
            get
            {
                var urlLocal = Request.Url.LocalPath.ToLower();
                if (!string.IsNullOrEmpty(urlLocal) && !urlLocal.EndsWith("/")) urlLocal = urlLocal.ToLower() + "/";
                else if (urlLocal == "/") urlLocal = string.Empty;
                return urlLocal;
            }
        }

        public Dictionary<string, UserRoleRecord> CurrentRules
        {
            get
            {
                if (CurrentUser.IsNullOrWhiteSpace()) return null;
                string groupId = CurrentUserRecord.GroupIdentifier;
                string userId = CurrentUserRecord.UserName;
                int departmentId = CurrentUserRecord.DepartmentId;
                Dictionary<string, UserRoleRecord> result = new Dictionary<string, UserRoleRecord>();
                return CacheFactory.MemCache.GetFromCache<Dictionary<string, UserRoleRecord>>(string.Format("[{0}].[{1}]", CACHING_NAME.CURRENT_USER_RECORD_RULES_DICT, userId), () =>
                {
                    List<ObjectRecord> menuitems = null;
                    Dictionary<string, UserRoleRecord> userDicts = new Dictionary<string, UserRoleRecord>();
                    Dictionary<string, GroupRoleRecord> groupDicts = new Dictionary<string, GroupRoleRecord>();
                    using (ServiceClient<IAuthentication> serviceClient = new ServiceClient<IAuthentication>())
                    {
                        RetrieveObjectsRequest request = new RetrieveObjectsRequest
                        {
                            Header = new RequestHeaderRecord
                            {
                                ApplicationKey = SystemConfiguration.ApplicationKey,
                                CallerName = "Authentication.Web.Controller.ControllerBase.CurrentRules"
                            }
                        };
                        var objectResponse = serviceClient.Proxy.RetrieveObjects(request);
                        if (objectResponse.IsSuccess)
                        {
                            menuitems = objectResponse.Records as List<ObjectRecord>;
                        }

                        if (menuitems != null)
                        {
                            menuitems = menuitems.OrderBy(x => x.ObjectIdentifier)/*OrderBy(x => x.OLEVEL).ThenBy(y => y.ORDER_ID).ThenBy(z => z.ID)*/
                                                 .RecursivelyData("ObjectIdentifier", "ParentIdentifier", "ObjectName").ToList();
                        }

                        if (userId != SystemConfiguration.SUPPER_ADMIN)
                        {
                            // Group rule
                            RetrieveGroupRolesRequest requestGroupRule = new RetrieveGroupRolesRequest
                            {
                                Header = new RequestHeaderRecord
                                {
                                    ApplicationKey = SystemConfiguration.ApplicationKey,
                                    CallerName = "Authentication.Web.Controller.ControllerBase.CurrentRules"
                                },
                                GroupIdentifier = groupId,
                                DepartmentId = departmentId
                            };
                            var groupRulesResponse = serviceClient.Proxy.RetrieveGroupRoles(requestGroupRule);
                            if (groupRulesResponse.IsSuccess && groupRulesResponse.Records != null)
                            {
                                foreach (GroupRoleRecord obj in groupRulesResponse.Records)
                                {
                                    var key = obj.GroupIdentifier + "$" + obj.ObjectIdentifier;
                                    if (!groupDicts.ContainsKey(key)) groupDicts.Add(key, obj);
                                }
                            }
                            // User rule
                            RetrieveUserRolesRequest requestUserRule = new RetrieveUserRolesRequest
                            {
                                Header = new RequestHeaderRecord
                                {
                                    ApplicationKey = SystemConfiguration.ApplicationKey,
                                    CallerName = "Authentication.Web.Controller.ControllerBase.CurrentRules"
                                },
                                UserName = userId
                            };
                            var userRulesResponse = serviceClient.Proxy.RetrieveUserRoles(requestUserRule);

                            if (userRulesResponse.IsSuccess && userRulesResponse.Records != null)
                            {
                                foreach (UserRoleRecord obj in userRulesResponse.Records)
                                {
                                    var key = obj.UserName + "#" + obj.ObjectIdentifier;
                                    if (!userDicts.ContainsKey(key)) userDicts.Add(key, obj);
                                }
                            }
                        }
                        if (menuitems != null)
                        {
                            foreach (var mitem in menuitems)
                            {
                                if (userId == SystemConfiguration.SUPPER_ADMIN)
                                {
                                    UserRoleRecord ruleAll = new UserRoleRecord
                                    {
                                        ApplicationKey = mitem.ApplicationKey,
                                        UserName = userId,
                                        ObjectIdentifier = mitem.ObjectIdentifier,
                                        Object = mitem,
                                        Active = true
                                    };
                                    result.Add(mitem.ObjectIdentifier, ruleAll);
                                    continue;
                                }
                                string keyGroup = groupId + "$" + mitem.ObjectIdentifier;
                                string keyUser = userId + "#" + mitem.ObjectIdentifier;
                                if (userDicts.ContainsKey(keyUser))
                                {
                                    result.Add(mitem.ObjectIdentifier, userDicts[keyUser]);
                                }
                                else if (groupDicts.ContainsKey(keyGroup))
                                {
                                    GroupRoleRecord group = groupDicts[keyGroup];
                                    UserRoleRecord ruleGroup = new UserRoleRecord
                                    {
                                        ApplicationKey = mitem.ApplicationKey,
                                        UserName = userId,
                                        ObjectIdentifier = mitem.ObjectIdentifier,
                                        Object = mitem,
                                        RoleIdentifier = group.RoleIdentifier,
                                        Active = true
                                    };
                                    result.Add(mitem.ObjectIdentifier, ruleGroup);
                                }
                            }
                        }
                    }
                    return result;
                }, SystemConfiguration.DefaultCacheTime);
            }
        }
        #endregion

        #region User Properties
        public string CurrentLang
        {
            get
            {
                return SiteLanguages.GetCurrentLanguage();
            }
        }
        public string CurrentUser
        {
            get
            {
                //return "admin";
                return Membership.CurrentUser;
            }
        }

        public UserRecord CurrentUserRecord
        {
            get
            {
                if (HttpContext.Items[HTTP_CONTEXT.CURRENT_USER_RECORD] == null)
                {
                    using (ServiceClient<IAuthentication> serviceClient = new ServiceClient<IAuthentication>())
                    {
                        RetrieveUserByUserNameRequest request = new RetrieveUserByUserNameRequest
                        {
                            Header = new RequestHeaderRecord
                            {
                                ApplicationKey = SystemConfiguration.ApplicationKey,
                                CallerName = "SMEsCustomer360Degree.Controllers.ControllerBase.CurrentUserRecord"
                            },
                            UserName = CurrentUser,
                            Lang = CurrentLang
                        };
                        var objectResponse = serviceClient.Proxy.RetrieveUserByUserName(request);
                        var record = new UserRecord();
                        record = objectResponse.Record;
                        if (objectResponse.Record == null)
                        {
                            record = new UserRecord
                            {
                                UserName = string.Empty,
                                ContractType = string.Empty,
                                Dao = string.Empty,
                                Branch = string.Empty,
                                Zone = string.Empty,
                                StartWorkingDate = DateTime.Now.Date.ToShortDateString(),
                            };
                        }
                        HttpContext.Items[HTTP_CONTEXT.CURRENT_USER_RECORD] = record;
                    }
                }
                return HttpContext.Items[HTTP_CONTEXT.CURRENT_USER_RECORD] as UserRecord;
            }
        }
        #endregion

        #region Helper Functions

        public string UploadFile(DirectoryInfo directory, HttpPostedFileBase file, string fileName = null)
        {
            string pathString = System.IO.Path.Combine(directory.ToString());

            bool isExists = System.IO.Directory.Exists(pathString);

            if (!isExists)
                System.IO.Directory.CreateDirectory(pathString);
            if (string.IsNullOrEmpty(fileName))
            {
                fileName = file.FileName;
            }
            var path = string.Format("{0}\\{1}", pathString, fileName);
            file.SaveAs(path);
            return path;
        }
        public static DataTable ConvertXSLXtoDataTable(string strFilePath, string fileExtension)
        {

            string excelConnectionString = string.Empty;
            excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
            strFilePath + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
            //connection String for xls file format.
            if (fileExtension == ".xls")
            {
                excelConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
                strFilePath + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
            }
            //connection String for xlsx file format.
            else if (fileExtension == ".xlsx")
            {
                excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
                strFilePath + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
            }

            DataSet ds = new DataSet();
            OleDbConnection oledbConn = new OleDbConnection(excelConnectionString);
            DataTable dt = new DataTable();
            try
            {
                //Create Connection to Excel work book and add oledb namespace
                oledbConn.Open();

                dt = oledbConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                if (dt == null)
                {
                    return null;
                }

                String[] excelSheets = new String[dt.Rows.Count];
                int t = 0;
                //excel data saves in temp file here.
                foreach (DataRow row in dt.Rows)
                {
                    excelSheets[t] = row["TABLE_NAME"].ToString();
                    t++;
                }
                OleDbConnection excelConnection1 = new OleDbConnection(excelConnectionString);


                string query = string.Format("Select * from [{0}]", excelSheets[0]);
                using (OleDbDataAdapter dataAdapter = new OleDbDataAdapter(query, excelConnection1))
                {
                    dataAdapter.Fill(ds);
                }
                dt = ds.Tables[0];
            }
            catch
            {
            }
            finally
            {

                oledbConn.Close();
            }
            return dt;
        }

        public static DataTable ToDataTable<T>(List<T> items)
        {
            DataTable dataTable = new DataTable(typeof(T).Name);

            //Get all the properties
            PropertyInfo[] Props = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);
            foreach (PropertyInfo prop in Props)
            {
                //prop.GetDisplayName(t=>t.name);
                //Setting column names as Property names
                dataTable.Columns.Add(prop.Name);
            }
            foreach (T item in items)
            {
                var values = new object[Props.Length];
                for (int i = 0; i < Props.Length; i++)
                {
                    //inserting property values to datatable rows
                    values[i] = Props[i].GetValue(item, null);
                }
                dataTable.Rows.Add(values);
            }
            //put a breakpoint here and check datatable
            return dataTable;
        }

        protected List<TreeViewNode> GetMenuNodes()
        {
            var result = CacheFactory.MemCache.GetFromCache<List<TreeViewNode>>(string.Format(CACHING_NAME.CURRENT_MENU_HEADER), () =>
            {
                var nodes = new List<TreeViewNode>();
                nodes = SystemConfiguration.ListMenuHeader.Select(x => new TreeViewNode
                {
                    Name = x.ObjectName,
                    Url = x.ObjectUrl
                }).ToList();
                return nodes;
            });
            return result;
        }

        public DateTime? GetBusinessDateByTableName(string name)
        {
            DateTime? date;
            using (ServiceClient<IAuthentication> serviceClient = new ServiceClient<IAuthentication>())
            {
                RetrieveDateBusinessRequest request = new RetrieveDateBusinessRequest
                {
                    Header = new RequestHeaderRecord
                    {
                        ApplicationKey = SystemConfiguration.ApplicationKey,
                        CallerName = "SMEsCustomer360Degree.BusinessDateInfo"
                    },
                    TableName = name
                };
                var response = serviceClient.Proxy.RetrieveDateBusiness(request);
                date = response.BusinessDate;
            }
            return date;
        }

        protected List<TreeViewNode> GetMenuNodes(string user)
        {
            //Get object with the rules
            IEnumerable<ObjectRecord> objectsSub = null;
            using (ServiceClient<IAuthentication> serviceClient = new ServiceClient<IAuthentication>())
            {
                RetrieveObjectsRequest request = new RetrieveObjectsRequest
                {
                    Header = new RequestHeaderRecord
                    {
                        ApplicationKey = SystemConfiguration.ApplicationKey,
                        CallerName = "Authentication.Web.Controller.ControllerBase.GetMenuNodes"
                    },
                    Language = SiteLanguages.GetCurrentLanguage()
                };
                var objectResponse = serviceClient.Proxy.RetrieveObjects(request);
                objectsSub = objectResponse.Records;
                objectsSub = objectsSub.Where(x => x.ActiveStatus.Equals("Y") || x.ActiveStatus.Equals("C") || x.ActiveStatus.Equals("D") || x.ActiveStatus.Equals("TBC") || x.ActiveStatus.Equals("New"));
            }

            var ruleDicts = this.CurrentRules;
            if (objectsSub != null && user != SystemConfiguration.SUPPER_ADMIN)
                objectsSub = objectsSub.Where(x => (ruleDicts.ContainsKey(x.ObjectIdentifier) && ruleDicts[x.ObjectIdentifier].Active == true && ruleDicts[x.ObjectIdentifier].RoleIdentifier == this.AccessRoleIdentifier)).ToList();


            if (objectsSub == null) return null;
            objectsSub = objectsSub.OrderBy(x => x.Olvel).ThenBy(x => x.ParentIdentifier).ThenBy(x => x.OrderId).ToList();


            //Bill to Dictionary
            Dictionary<string, TreeViewNode> dict = new Dictionary<string, TreeViewNode>();
            var nodes = new List<TreeViewNode>();
            //bool access = false;//Authentication values
            foreach (var obj in objectsSub)
            {
                //if (user == SystemConfiguration.SUPPER_ADMIN ||
                //(obj.ObjectUrl != null && CurrentURL.ToUpper().Contains(obj.ObjectUrl.ToUpper())) ||
                //ALLOW_PAGES.Contains(CurrentURL)) access = true;
                //if (obj.Olvel == 1)
                //{
                var node = new TreeViewNode
                {
                    Name = obj.ObjectIdentifier,
                    Value = obj.ObjectName,
                    Url = obj.ObjectUrl,
                    CssIcon = obj.ObjectIcon,
                    Controller = obj.ObjectController,
                    Action = obj.ObjectAction,
                    IdCss = obj.IdCss,
                    ActiveStatus = obj.ActiveStatus,
                    ParentId = obj.ParentIdentifier,
                    OrderId = obj.OrderId
                };
                if (!dict.ContainsKey(node.Name)) dict.Add(node.Name, node);
                //}

            }
            //if (string.IsNullOrEmpty(CurrentURL)) access = true;

            ////Authentication on Menu system
            //if (!access)
            //{
            //    //Redirect to home if can not access
            //    HttpContext.Response.Redirect("~/");
            //    return null;
            //}

            /*if (!CurrentURL.ToUpper().Contains("/ACCOUNT/CHANGEPASSWORD") && sysService.CheckChangePassword(paramsv.CURRENT_USER.USER_NAME, paramsv.CURRENT_USER.PASS))
            {
                //Redirect to home if can not access
                HttpContext.Response.Redirect("~/account/changepassword");
                return null;
            }*/

            //bill to the tree
            foreach (var obj in objectsSub)
            {
                if (obj.IsSubstitution == true) continue;
                if (string.IsNullOrEmpty(obj.ParentIdentifier) || (!dict.ContainsKey(obj.ParentIdentifier)))
                {
                    if (nodes.Any(x => x.ParentId.Equals(obj.ParentIdentifier)))
                    {
                        nodes.Where(x => x.ParentId.Equals(obj.ParentIdentifier)).FirstOrDefault().Nodes.Add(dict[obj.ObjectIdentifier]);
                    }
                    else if (dict.ContainsKey(obj.ObjectIdentifier))
                    {
                        var node = dict[obj.ObjectIdentifier];
                        nodes.Add(node);
                    }
                }
                else
                {
                    var subNode = dict[obj.ParentIdentifier];
                    subNode.Nodes.Add(dict[obj.ObjectIdentifier]);
                    nodes.Where(x => x.ParentId.Equals(obj.ParentIdentifier)).ToList().ForEach(x => x = subNode);
                }
            }
            nodes = nodes.OrderBy(x => x.OrderId).ToList();
            return nodes;
        }

        protected override void OnActionExecuting(ActionExecutingContext filterContext)
        {
            base.OnActionExecuting(filterContext);
            if (filterContext.HttpContext.Request.HttpMethod != "POST"
                && !CurrentURL.IsNullOrWhiteSpace() && CurrentURL != "/" && !ALLOW_PAGES.Any(x => CurrentURL.ToUpper().Contains(x))
                && !CurrentUser.IsNullOrWhiteSpace() && CurrentUser != SystemConfiguration.SUPPER_ADMIN)
            {
                var url = filterContext.HttpContext.Request.RawUrl.ToLower();// ====> /admin/manage
                var menuAllow = this.CurrentRules.Where(x => !x.Value.Object.ObjectUrl.IsNullOrWhiteSpace()).Select(x => x.Value.Object.ObjectUrl.EndsWith("/") ? x.Value.Object.ObjectUrl.ToLower() : x.Value.Object.ObjectUrl.ToLower() + "/").ToList();

                var Active = menuAllow.Any(x => x != "/" && CurrentURL.Contains(x));
                if (!Active) HttpContext.Response.Redirect("/Error/NotFound", true);
            }
        }

        protected override void OnActionExecuted(ActionExecutedContext filterContext)
        {
            string actionName = filterContext.ActionDescriptor.ActionName;
            string controllerName = filterContext.ActionDescriptor.ControllerDescriptor.ControllerName;
            var vm = filterContext.Controller.ViewData.Model as IViewModel;
            var modelFull = string.Format("{0}.{1}", controllerName, actionName);
            if (vm != null && !vm.Message.IsNullOrWhiteSpace())
            {
                ModelState.AddModelError(modelFull, vm.Message);
            }
            base.OnActionExecuted(filterContext);
        }

        public void WriteLogUser(string controller, string action, string userName)
        {
            using (ServiceClient<IAuthentication> serviceClient = new ServiceClient<IAuthentication>())
            {
                ActionLogRecord log = new ActionLogRecord()
                {
                    Controller = controller,
                    Action = action,
                    Ip = HttpContext.Request.UserHostAddress,
                    Datetime = DateTime.Now,
                    Username = userName
                };
                SaveActionLogRequest requestLog = new SaveActionLogRequest
                {
                    Header = new RequestHeaderRecord
                    {
                        ApplicationKey = SystemConfiguration.ApplicationKey,
                        CallerName = "SMEsCustomer360Degree.SaveLog"
                    },
                    Record = log
                };
                var responseLog = serviceClient.Proxy.SaveActionLog(requestLog);
            }
        }

        public static string GetEnumDescription(Enum value)
        {
            FieldInfo fi = value.GetType().GetField(value.ToString());
            DescriptionAttribute[] attributes =
                (DescriptionAttribute[])fi.GetCustomAttributes(typeof(DescriptionAttribute), false);
            if (attributes != null && attributes.Length > 0)
                return attributes[0].Description;
            else
                return value.ToString();
        }

        protected List<string> GetListDaoManager()
        {
            var result = CacheFactory.MemCache.GetFromCache<List<string>>(string.Format(CACHING_NAME.LIST_DAO_MANAGER), () =>
            {
                var nodes = new List<string>();

                List<UserRecord> resultCustomer = new List<UserRecord>();
                using (ServiceClient<IAuthentication> serviceClient = new ServiceClient<IAuthentication>())
                {
                    FilterRecord record = new FilterRecord();
                    RetrieveListMsboRequest request = new RetrieveListMsboRequest
                    {
                        Header = new RequestHeaderRecord
                        {
                            ApplicationKey = SystemConfiguration.ApplicationKey,
                            CallerName = "SMEsCustomer360Degree.Controllers.CustomerManagerController.ListBranch"
                        },
                        CurrentUser = CurrentUserRecord,
                        Filter = record
                    };
                    var customerRecords = new RetrieveListMsboResponse();
                    customerRecords = serviceClient.Proxy.RetrieveListMsbo(request);
                    resultCustomer = (customerRecords == null || customerRecords.Records == null) ? new List<UserRecord>() : customerRecords.Records;
                }
                nodes = resultCustomer.Select(x => x.Dao).ToList();
                return nodes;
            });
            return result;
        }

        protected List<BranchRecord> GetListBranches()
        {
            var result = CacheFactory.MemCache.GetFromCache<List<BranchRecord>>(string.Format(CACHING_NAME.LIST_BRANCH), () =>
            {
                List<BranchRecord> resultCustomer = new List<BranchRecord>();
                using (ServiceClient<ICustomer> serviceClient = new ServiceClient<ICustomer>())
                {
                    RetrieveListBranchRequest request = new RetrieveListBranchRequest
                    {
                        Header = new RequestHeaderRecord
                        {
                            ApplicationKey = SystemConfiguration.ApplicationKey,
                            CallerName = "SMEsCustomer360Degree.Controllers.CustomerManagerController.ListBranch"
                        },
                        Dao = new UserRecord
                        {
                            UserName = "guest",
                            Password = "****",
                            GroupIdentifier = "1"
                        }
                    };
                    var customerRecords = new RetrieveListBranchResponse();
                    customerRecords = serviceClient.Proxy.RetrieveListBranch(request);
                    resultCustomer = (customerRecords == null || customerRecords.Records == null) ? new List<BranchRecord>() : customerRecords.Records;
                }
                return resultCustomer;
            });
            return result;
        }

        protected void ExportToExcel(DataTable dt, string fileName)
        {
            using (XLWorkbook wb = new XLWorkbook())
            {
                wb.Worksheets.Add(dt, CurrentUserRecord.Branch);
                wb.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
                wb.Style.Font.Bold = true;
                Response.Clear();
                Response.Buffer = true;
                Response.Charset = "";
                Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                Response.AddHeader("content-disposition", "attachment;filename= " + fileName);
                Response.ContentEncoding = Encoding.Unicode;
                using (MemoryStream MyMemoryStream = new MemoryStream())
                {
                    wb.SaveAs(MyMemoryStream);
                    MyMemoryStream.WriteTo(Response.OutputStream);
                    Response.Flush();
                    Response.End();
                }
            }
        }
        protected void ExportMultiSheetToExcel(List<DataTable> dt, string fileName)
        {
            using (XLWorkbook wb = new XLWorkbook())
            {
                foreach (var item in dt)
                {
                    wb.Worksheets.Add(item, item.TableName);
                }
                wb.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
                wb.Style.Font.Bold = true;
                Response.Clear();
                Response.Buffer = true;
                Response.Charset = "";
                Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                Response.AddHeader("content-disposition", "attachment;filename= " + fileName);
                Response.ContentEncoding = Encoding.Unicode;
                using (MemoryStream MyMemoryStream = new MemoryStream())
                {
                    wb.SaveAs(MyMemoryStream);
                    MyMemoryStream.WriteTo(Response.OutputStream);
                    Response.Flush();
                    Response.End();
                }
            }
        }

        protected FilterRecord GetBaseFilter(List<string> searchColumn)
        {
            FilterRecord record = new FilterRecord();
            switch (CurrentUserRecord.GroupIdentifier)
            {
                case "1":
                    if (searchColumn != null)
                    {
                        record.Region = searchColumn[0];
                        record.Branch = searchColumn[1];
                        record.Dao = searchColumn[2];
                        record.DaoName = searchColumn[3];
                        record.Cif = searchColumn[4];
                        record.CustName = searchColumn[5];
                        record.Industry = searchColumn[6];
                    }
                    break;
                case "0":
                    if (searchColumn != null)
                    {
                        record.Region = searchColumn[0];
                        record.Branch = searchColumn[1];
                        record.Dao = searchColumn[2];
                        record.DaoName = searchColumn[3];
                        record.Cif = searchColumn[4];
                        record.CustName = searchColumn[5];
                        record.Industry = searchColumn[6];
                    }
                    break;
                case "2":
                    record.Region = CurrentUserRecord.Zone;
                    if (searchColumn != null)
                    {
                        record.Branch = searchColumn[0];
                        record.Dao = searchColumn[1];
                        record.DaoName = searchColumn[2];
                        record.Cif = searchColumn[3];
                        record.CustName = searchColumn[4];
                        record.Industry = searchColumn[5];
                    }
                    break;
                case "3":
                    record.Branch = CurrentUserRecord.BranchId;
                    if (searchColumn != null)
                    {
                        if (CurrentUserRecord.Position.Equals("MBOM") || CurrentUserRecord.Position.Equals("SBOM"))
                        {

                            if (string.IsNullOrWhiteSpace(searchColumn[0]) || searchColumn[0].Trim() == "null")
                            {
                                record.Dao = string.Join(",", GetListDaoManager().ToArray());
                            }
                            else
                            {
                                record.Dao = searchColumn[0];
                            }
                            if (string.IsNullOrWhiteSpace(searchColumn[1]) || searchColumn[1].Trim() == "null")
                            {
                                record.DaoName = string.Join(",", GetListDaoManager().ToArray());
                            }
                            else
                            {
                                record.DaoName = searchColumn[1];
                            }
                        }
                        else
                        {
                            record.Dao = searchColumn[0];
                            record.DaoName = searchColumn[1];
                        }
                        record.Cif = searchColumn[2];
                        record.CustName = searchColumn[3];
                        record.Industry = searchColumn[4];
                    }
                    if (!string.IsNullOrEmpty(CurrentUserRecord.Team))
                    {
                        record.Team = CurrentUserRecord.Team;
                    }
                    break;
                case "4":
                    if (searchColumn != null)
                    {
                        record.Cif = searchColumn[0];
                        record.CustName = searchColumn[1];
                        record.Industry = searchColumn[2];
                    }
                    record.Dao = CurrentUserRecord.Dao;
                    break;
                default:
                    break;
            }
            return record;
        }
        #endregion

        #region Cookie

        public void CreateCookie(string cookieName, string cookieValue)
        {
            var cookie = new HttpCookie(cookieName);
            cookie.Value = cookieValue;
            cookie.Expires = DateTime.Now.AddMinutes(5);
            Response.Cookies.Add(cookie);
        }

        public void RemoveCookie(string cookieName)
        {
            var cookie = new HttpCookie("cookieName");
            cookie.Expires = DateTime.Now.AddDays(-1d);
            Response.Cookies.Add(cookie);
        }
        #endregion
    }
}